package sweet.helper.jdbc

import sweet.helper._
import java.sql._
import javax.sql._
import org.apache.commons.dbcp._

/** A bare simple implementation of javax.sql.DataSource where you already has
 * a connection object(perhaps you got it from DriverManager. This provide
 * standalone program that needed to use JdbcSupport in a quickway. */
class SingleConnectionDataSource(val conn: Connection) extends DataSource {
  def getConnection = conn
  def getConnection(user: String, pass: String) = conn
  def getLoginTimeout = throw new UnsupportedOperationException("Not implemented.")
  def getLogWriter = throw new UnsupportedOperationException("Not implemented.")
  def setLoginTimeout(timeout: Int) = throw new UnsupportedOperationException("Not implemented.")
  def setLogWriter(writer: java.io.PrintWriter) = throw new UnsupportedOperationException("Not implemented.")
  def isWrapperFor(iface: Class[_]) = false
  def unwrap[A](iface: Class[A]): A = throw new UnsupportedOperationException("Not implemented.")
}

/** Default implementation of JdbcSupport that provide dataSource pooling using Apache commons-dbcp.
 */
class JdbcHelper(username: String, password: String, url: String, driver: String) extends JdbcSupport {
  def dataSource: DataSource = {    
    val ds = new BasicDataSource
    ds.setUsername(username)
    ds.setPassword(password)
    ds.setUrl(url)
    ds.setDriverClassName(driver)
    ds
  }
  /** override with val for faster access. Default will always close conn after use.*/
  override val closeConnAfterUse = true
}
/** Companion object.*/
object JdbcHelper{
  def apply(username: String, password: String, url: String, driver: String) =
    new JdbcHelper(username, password, url, driver)
}

/** An ResultSet helper class. This class operations will NOT close resultSet object, except
 * the #close method itself.*/
class ResultSetHelper(val rs: ResultSet){
  
  /** retrieve object from result set then call toString using colIdx, which is 1 based index. */
  def apply(colIdx: Int) = get(colIdx).toString
  /** retrieve object from result set then call toString using colName. */
  def apply(colName: String) = get(colName).toString
    
  /** Loop through each column in current resultSet row and calling user func with 1 based index
   * and it's object value. */
  def eachColWithIdx(func: (Int, AnyRef) => Unit){
    val rsmd = rs.getMetaData
    val max = rsmd.getColumnCount
    for(idx <- 1 to max) 
      func(idx, rs.getObject(idx))
  }
  
  /** Loop through each column in current resultSet row and calling user func with colName
   * and it's object value. */
  def eachColWithName(func: (String, AnyRef) => Unit){
    val rsmd = rs.getMetaData
    val max = rsmd.getColumnCount
    for(idx <- 1 to max)
      func(rsmd.getColumnName(idx), rs.getObject(idx))
  }
  
  /** Loop through each row of resultset and call each col  with rowIdx and colIdx, both are 1 based index. */
  def eachRowWithIdx(func: (Int, Int, AnyRef) => Unit){
    rs.beforeFirst()
    var rowIdx = 1
    while(rs.next){
      val rsmd = rs.getMetaData
      val max = rsmd.getColumnCount
      for(idx <- 1 to max)
        func(rowIdx, idx, rs.getObject(idx))
      rowIdx +=1
    }
  }
  
  /** Loop through each row of resultset and call each col
   * with rowIdx (1 based index, and colName. */
  def eachRowWithName(func: (Int, String, AnyRef) => Unit){
    rs.beforeFirst()
    var rowIdx = 1
    while(rs.next){
      val rsmd = rs.getMetaData
      val max = rsmd.getColumnCount
      for(idx <- 1 to max)
        func(rowIdx, rsmd.getColumnName(idx), rs.getObject(idx))
      rowIdx +=1
    }
  }
  
  /** Retrive resultSet's object value using 1 based index. */
  def get(idx: Int) = rs.getObject(idx)
  
  /** Retrive resultSet's object value using colName. */
  def get(name: String) = rs.getObject(name)
  
  /** Move resultSet to next row. */
  def next = rs.next
  
  /** Close underline/wrapped resultSet */
  def close = rs.close()
}

/** Provide basic jdbc operations in scala friendly way. Subclass
 * need to provide a #dataSource implementation.*/
trait JdbcSupport extends Logger {
  
  /** Subclass need to provide this */
  def dataSource: DataSource
  
  /** Flag to close the connection after each jdbc operation or not. */
  def closeConnAfterUse = true
  
  private def withRS(rs: ResultSet)(func: =>Unit){    
    try{ func }
    finally{ rs.close }  
  }
  
  private def withRS(rs: ResultSetHelper)(func: =>Unit){    
    try{ func }
    finally{ rs.close }  
  }
  
  /** Let subclass have instance of JdbcSupport that will keep conn remain
   * open until user func is ended.*/
  def withOpenJdbc(func: JdbcSupport =>Unit) {
    withConn{ conn =>
      conn.setAutoCommit(false)
      val jdbcSupport = new JdbcSupport(){
        def dataSource = new SingleConnectionDataSource(conn)
        override val closeConnAfterUse = false
      }
      func(jdbcSupport)
    }
  }
  
  /** Same as #withOpenConn, but will only commit changes if user func succeeded without exception. */
  def withTxJdbc(func: JdbcSupport =>Unit) {
    withConn{ conn =>
      conn.setAutoCommit(false)
      val jdbcSupport = new JdbcSupport(){
        def dataSource = new SingleConnectionDataSource(conn)
        override val closeConnAfterUse = false
      }
      try{ 
        func(jdbcSupport)
        logger.debug("Commiting transaction.")
        conn.commit
      }
      catch{ case ex @ _ => 
        logger.warn("Rolling back transactions.", ex)
        conn.rollback
        throw ex
      }
    }
  }
  
  /** Let subclass have full control of conn object.
   * This class will properly close the conn after the method is completed. */
  def withConn(func: Connection => Unit){
    val newConn = dataSource.getConnection
    try{ func(newConn) }finally{ if(closeConnAfterUse) newConn.close } 
  }
  
  
  /** Let subclass have full controll on a statement object.
   * This class will properly close the stmt after the method is completed. */
  def withStmt(func: Statement => Unit){
    withConn{ conn =>
      val stmt = conn.createStatement
      try{ func(stmt) }finally{ stmt.close }
    }
  }
  
  /** Let subclass have full controll on a prepared statement object.
   * This class will properly close the pstmt after the method is completed. */
  def withPStmt(sql: String)(func: PreparedStatement => Unit){
    logger.debug("Preparing: "+sql)
    withConn{ conn =>
      val pstmt = conn.prepareStatement(sql)
      try{ func(pstmt) }finally{ pstmt.close }
    }
  }
   
  /** Execute a single update query with possible parameters. 
   * Method will return number of record updated.*/ 
  def update(sql: String, params: Any*): Int ={
    var res = 0
    withPStmt(sql){ stmt =>
      logger.debug("Params: " + params)
      
      val indexedParams = params.toArray.zip( (1 to params.size).toArray )
      indexedParams.foreach{ x =>
        val (i,p) = x
        stmt.setObject(p, i) 
      }
      res = stmt.executeUpdate()
    }
    res
	}
    
  /** Execute a single select query with possible parameters. 
   * Method will return number of record found. Each ResultSet found is 
   * wraped by ResultSetHelper and pass to user func. */
  def query(sql: String, params: Any*)(func: ResultSetHelper=>Unit){ 
    var count = 0    
    withPStmt(sql){ pstmt =>
      val paramAry = params.toArray
      for((p,i) <- paramAry.zipWithIndex){
        val idx = i+1
        logger.debug("Params("+idx+"): " + paramAry(i))
        pstmt.setObject(idx, p)
      }
      val rs = pstmt.executeQuery()      
      withRS(rs){ 
        while(rs.next){
          func(new ResultSetHelper(rs))         
          count += 1
        }
      }        
    }
    count
  }
  
  /** Execute a single select query with possible parameters. If result
   * has more rows than limit, it will only execute user func up to that number
   * of row of records. The rest is dropped.
   * Method will return number of record found. Each ResultSet found is 
   * wraped by ResultSetHelper and pass to user func. */
  def queryLimit(limit: Int, sql: String, params: Any*)(func: ResultSetHelper=>Unit): Int = { 
    var count = 0    
    withPStmt(sql){ pstmt =>
      val paramAry = params.toArray
      for((p,i) <- paramAry.zipWithIndex){
        val idx = i+1
        logger.debug("Params("+idx+"): " + paramAry(i))
        pstmt.setObject(idx, p)
      }
      val rs = pstmt.executeQuery()   
      withRS(rs){ 
        while(count<limit && rs.next){
          func(new ResultSetHelper(rs)) 
          count += 1
        }
      }
    }
    count //return the number of row performed.
  }
  
  /** Execute a single select query with possible parameters and return optional single value.
   * caller may supply custom extractor to convert the ResultSetHelper intance for the value
   * needed.*/
  def queryOptVal[A](sql: String, params: Any*)(extractor: (ResultSetHelper=>A)): Option[A] = {
    var res: A = null.asInstanceOf[A]
    val count = queryLimit(1, sql, params: _*){ rs => res = extractor(rs) }
    if(count == 0)
      None
    Some(res)
  }
  
  /** Same as #queryOptVal but throw exception if record not found. */
  def queryVal[A](sql: String, params: Any*)(extractor: (ResultSetHelper=>A)): A = {
    queryOptVal[A](sql, params: _*)(extractor) match {
      case None => throw new SQLException("There is no result from query.")
      case Some(res) => res
    }
  }
  
  /** Get a single optional Integer value from a query.*/
  def queryOptInt(sql: String, params: Any*): Option[Int] = queryOptVal[Int](sql, params: _*){ rsh => rsh.rs.getInt(1) }
  /** Same as #queryOptInt but throw exception if record not found. */
  def queryInt(sql: String, params: Any*): Int = queryVal[Int](sql, params: _*){ rsh => rsh.rs.getInt(1) }
  
  /** Get a single optional Double value from a query.*/
  def queryOptDouble(sql: String, params: Any*): Option[Double] = queryOptVal[Double](sql, params: _*){ rsh => rsh.rs.getDouble(1) }
  /** Same as #queryDouble but throw exception if record not found. */
  def queryDouble(sql: String, params: Any*): Double = queryVal[Double](sql, params: _*){ rsh => rsh.rs.getDouble(1) }
  
  /** Get a single optional Boolean value from a query.*/
  def queryOptBoolean(sql: String, params: Any*): Option[Boolean] = queryOptVal[Boolean](sql, params: _*){ rsh => rsh.rs.getBoolean(1) }
  /** Same as #queryOptBoolean but throw exception if record not found. */
  def queryBoolean(sql: String, params: Any*): Boolean = queryVal[Boolean](sql, params: _*){ rsh => rsh.rs.getBoolean(1) }
  
  /** Get a single optional String value from a query.*/
  def queryOptString(sql: String, params: Any*): Option[String] = queryOptVal[String](sql, params: _*){ rsh => rsh.rs.getString(1) }
  /** Same as #queryOptString but throw exception if record not found. */
  def queryString(sql: String, params: Any*): String = queryVal[String](sql, params: _*){ rsh => rsh.rs.getString(1) }
  
  /** Get a single optional java.util.Date value from a query.*/
  def queryOptDate(sql: String, params: Any*): Option[java.util.Date] = queryOptVal[java.util.Date](sql, params: _*){ rsh => rsh.rs.getDate(1) }
  /** Same as #queryOptDate but throw exception if record not found. */
  def queryDate(sql: String, params: Any*): java.util.Date = queryVal[java.util.Date](sql, params: _*){ rsh => rsh.rs.getDate(1) }
  
  /** Execute a single select query with possible parameters and return an optional row of record as Map.
   * All column names are used as keys.*/
  def queryOptMap[A](sql: String, params: Any*): Option[scala.collection.Map[String, Any]] = {
    var res = new scala.collection.mutable.HashMap[String, Any]
    val count = queryLimit(1, sql, params: _*){ rs => rs.eachColWithName{ (name, value) => res(name) = value } }
    if(count == 0)
      None
    Some(res.readOnly)
  }
  /** Same as #queryOptMap, but throw exception if record not found. */
  def queryMap[A](sql: String, params: Any*): scala.collection.Map[String, Any] = {
    queryOptMap(sql, params: _*) match {
      case None => throw new SQLException("There is no result from query.")
      case Some(res) => res
    }
  }  
  
  /** Execute a single select query with possible parameters and return List of all row of record as Map.
   * all column names are used as keys.*/
  def queryList[A](sql: String, params: Any*): List[scala.collection.Map[String, Any]] = {
    var res = new scala.collection.mutable.ListBuffer[scala.collection.Map[String, Any]]
    query(sql, params: _*){ rs =>
      var map = new scala.collection.mutable.HashMap[String, Any]
      rs.eachColWithName{ (name, value) => map(name) = value }
      res += map.readOnly
    }
    res.readOnly.toList
  }
}
